-- SQL code to populate each table with sample data

/* Insert Data INTO Region */

INSERT INTO Region (RegionCode, RegionName) VALUES ('UK', 'United Kingdom')
INSERT INTO Region (RegionCode, RegionName) VALUES ('US', 'United States')
INSERT INTO Region (RegionCode, RegionName) VALUES ('BG', 'British Virgin Islands')
INSERT INTO Region (RegionCode, RegionName) VALUES ('UG', 'Uganda')

GO

-- SELECT DATA FROM REGION

SELECT RegionCode, RegionName FROM Region; 
GO

/* Insert Data INTO City */

INSERT INTO City (CityCode, CityName, CountyName, RegionCode) VALUES ('SWA', 'Swansea','West Glamorgan','UK')
INSERT INTO City (CityCode, CityName, CountyName, RegionCode) VALUES ('CAR', 'Cardiff','South Glamorgan','UK')
INSERT INTO City (CityCode, CityName, CountyName, RegionCode) VALUES ('LDN', 'London','Greater London','UK')
INSERT INTO City (CityCode, CityName, CountyName, RegionCode) VALUES ('CMN', 'Carmarthen','Carmarthenshire','UK')

GO

-- SELECT DATA FROM CITY

SELECT CityCode, CityName, CountyName, RegionCode FROM City; 
Go

/* Insert Data INTO Gym */

INSERT INTO Gym (GymBranchID, Name, Address, CityCode, Postcode, TelephoneNo) VALUES ('G0001','Shape up Gyms ','Queens Street','CAR','CF10 2BH','02920020000')
INSERT INTO Gym (GymBranchID, Name, Address, CityCode, Postcode, TelephoneNo) VALUES ('G0002','Shape up Gyms ','Waterloo Road','LDN','SE18UX','02079460800')
INSERT INTO Gym (GymBranchID, Name, Address, CityCode, Postcode, TelephoneNo) VALUES ('G0003','Shape up Gyms ','Parc Tawe Retail Park','SWA','SA12AL','01792341034')
INSERT INTO Gym (GymBranchID, Name, Address, CityCode, Postcode, TelephoneNo) VALUES ('G0004','Shape up Gyms ','Nantgaredig','CMN','SA327NY','01267290238')

GO

-- DELETE DATA FROM GYM

DELETE FROM Gym;

-- SELECT DATA FROM GYM

SELECT GymBranchID, Name, Address, CityCode, Postcode, TelephoneNo FROM Gym; 
Go

--DELETE DATA FROM MEMBERSHIP TYPE

DELETE FROM MembershipType;


/* Insert Data INTO Membership Type*/

INSERT INTO MembershipType (MembershipTypeID, PackageName, Description, ContractType, DurationType, DurationQuantity, OffPeakMembership, CancelAnytime) VALUES ('01','FIT X','A Popular membership, providing full access to all facilities within your home club, plus multi-site access across a selected number of clubs within the UK','Fixed','Monthly','12 Month','No','NO')
INSERT INTO MembershipType (MembershipTypeID, PackageName, Description, ContractType, DurationType, DurationQuantity, OffPeakMembership, CancelAnytime) VALUES ('02','FIT X Extra','A Popular membership for those wishing ultimate flexibility without locking into a longer term contract offering access to clubs within the UK','Periodic','Monthly','1 Month','No','YES')
INSERT INTO MembershipType (MembershipTypeID, PackageName, Description, ContractType, DurationType, DurationQuantity, OffPeakMembership, CancelAnytime) VALUES ('03','FIT X Student','This is our most popular package and secures your price for 12 Months with members benifits.','Periodic','Monthly','12 Month','No','YES')
INSERT INTO MembershipType (MembershipTypeID, PackageName, Description, ContractType, DurationType, DurationQuantity, OffPeakMembership, CancelAnytime) VALUES ('04','1 Day Gym or Class Pass','This provides an opportunity to demonstrate available facilities  before applying for membership.','Periodic','Daily','1 Day','Yes','NA')

-- SELECT DATA FROM MEMBERSHIPTYPE

SELECT MembershipTypeID, PackageName, Description, ContractType, DurationType, DurationQuantity, OffPeakMembership, CancelAnytime FROM MembershipType;
Go

--DELETE DATA FROM GYMMEMBERSHIP PRICE

DELETE FROM GymMembershipPrice;

/* Insert Data INTO GymMembershipPrice*/

INSERT INTO GymMembershipPrice (MembershipTypeID, GymID, OneTimePaymentFee, MonthlyPaymentFee, JoiningFee) VALUES ('01','G0001','456.00','54.00','15.00')
INSERT INTO GymMembershipPrice (MembershipTypeID, GymID, OneTimePaymentFee, MonthlyPaymentFee, JoiningFee) VALUES ('02','G0001','420.00','35.00','15.00')
INSERT INTO GymMembershipPrice (MembershipTypeID, GymID, OneTimePaymentFee, MonthlyPaymentFee, JoiningFee) VALUES ('03','G0001','384.00','32.00','00.00')
INSERT INTO GymMembershipPrice (MembershipTypeID, GymID, OneTimePaymentFee, MonthlyPaymentFee, JoiningFee) VALUES ('04','G0001','6.25','0.00','00.00')
INSERT INTO GymMembershipPrice (MembershipTypeID, GymID, OneTimePaymentFee, MonthlyPaymentFee, JoiningFee) VALUES ('01','G0002','456.00','54.00','15.00')
INSERT INTO GymMembershipPrice (MembershipTypeID, GymID, OneTimePaymentFee, MonthlyPaymentFee, JoiningFee) VALUES ('02','G0002','420.00','35.00','15.00')
INSERT INTO GymMembershipPrice (MembershipTypeID, GymID, OneTimePaymentFee, MonthlyPaymentFee, JoiningFee) VALUES ('03','G0002','384.00','32.00','00.00')
INSERT INTO GymMembershipPrice (MembershipTypeID, GymID, OneTimePaymentFee, MonthlyPaymentFee, JoiningFee) VALUES ('04','G0002','6.25','0.00','00.00')
INSERT INTO GymMembershipPrice (MembershipTypeID, GymID, OneTimePaymentFee, MonthlyPaymentFee, JoiningFee) VALUES ('01','G0003','456.00','54.00','15.00')
INSERT INTO GymMembershipPrice (MembershipTypeID, GymID, OneTimePaymentFee, MonthlyPaymentFee, JoiningFee) VALUES ('02','G0003','420.00','35.00','15.00')
INSERT INTO GymMembershipPrice (MembershipTypeID, GymID, OneTimePaymentFee, MonthlyPaymentFee, JoiningFee) VALUES ('03','G0003','384.00','32.00','00.00')
INSERT INTO GymMembershipPrice (MembershipTypeID, GymID, OneTimePaymentFee, MonthlyPaymentFee, JoiningFee) VALUES ('04','G0003','6.25','0.00','00.00')
INSERT INTO GymMembershipPrice (MembershipTypeID, GymID, OneTimePaymentFee, MonthlyPaymentFee, JoiningFee) VALUES ('01','G0004','456.00','54.00','15.00')
INSERT INTO GymMembershipPrice (MembershipTypeID, GymID, OneTimePaymentFee, MonthlyPaymentFee, JoiningFee) VALUES ('02','G0004','420.00','35.00','15.00')
INSERT INTO GymMembershipPrice (MembershipTypeID, GymID, OneTimePaymentFee, MonthlyPaymentFee, JoiningFee) VALUES ('03','G0004','384.00','32.00','00.00')
INSERT INTO GymMembershipPrice (MembershipTypeID, GymID, OneTimePaymentFee, MonthlyPaymentFee, JoiningFee) VALUES ('04','G0004','6.25','0.00','00.00')

GO

-- SELECT DATA FROM GYMMEMBERSHIP PRICE

SELECT MembershipTypeID, GymID, OneTimePaymentFee, MonthlyPaymentFee, JoiningFee FROM GymMembershipPrice; 
Go

/* Insert Data INTO Customer*/

insert into Customer (Customerid, GymID, FirstName, Lastname, DOB, Email, Passcode, Homeaddress, HomeCityCode, PostCode, ContactPhoneNo, MedicalSummary, JoinDate, BankAccountNo, SortCode) values ('01', 'G0001','Jade','Lawson','02/14/1982','jlawson0@redcross.org','9dIpWF1tsB','6884 Sheridan Crossing Cardiff','UK','SA17 3EF','07054250498','Arthritis','12/25/2000','31510604','100000');
insert into Customer (Customerid, GymID, FirstName, Lastname, DOB, Email, Passcode, Homeaddress, HomeCityCode, PostCode, ContactPhoneNo, MedicalSummary, JoinDate, BankAccountNo, SortCode) values ('02', 'G0002','Suha','Daher','10/25/1993','SuhaHawazinDaher@armyspy.com','1234567890','63 Old Chapel Road London','UK','LD6 3PE','07787229459','Arthritis','12/25/2005','29823529','902127');
insert into Customer (Customerid, GymID, FirstName, Lastname, DOB, Email, Passcode, Homeaddress, HomeCityCode, PostCode, ContactPhoneNo, MedicalSummary, JoinDate, BankAccountNo, SortCode) values ('03', 'G0003','Bradley','Gardiner','	12/9/1979','u1g9p8g1m2@groupbuff.com','LP925403B','89 Golf Road Carmarthen','UK','SA1 4LR','07808954054','Diabetes','12/25/2010','75849855','200052');
insert into Customer (Customerid, GymID, FirstName, Lastname, DOB, Email, Passcode, Homeaddress, HomeCityCode, PostCode, ContactPhoneNo, MedicalSummary, JoinDate, BankAccountNo, SortCode) values ('04', 'G0004','Spencer','Tyler','3/20/1953','y7tezid0ute@groupbuff.com','YN029728A','59 Newport Road Swansea','UK','SA3 18AP','07889350339','NA','01/05/2020','12098709','185008');
insert into Customer (Customerid, GymID, FirstName, Lastname, DOB, Email, Passcode, Homeaddress, HomeCityCode, PostCode, ContactPhoneNo, MedicalSummary, JoinDate, BankAccountNo, SortCode) values ('05', 'G0001','Leo','Dean','	11/13/1978','v8yzl6jd9da@powerencry.com','ST990446C','12 Greyfriars Road Cardiff','UK','CF1 6DJ','07755034300','No Medical','11/13/2019','20400952','826632');
insert into Customer (Customerid, GymID, FirstName, Lastname, DOB, Email, Passcode, Homeaddress, HomeCityCode, PostCode, ContactPhoneNo, MedicalSummary, JoinDate, BankAccountNo, SortCode) values ('06', 'G0002','Scarlett','Manning','7/1/1981','wh09pbmq6w@meantinc.com','OZ612862D','15 Guild Street London','UK','SE16 6UR','07782114353','Stroke','01/28/2006','20400952','826632');
insert into Customer (Customerid, GymID, FirstName, Lastname, DOB, Email, Passcode, Homeaddress, HomeCityCode, PostCode, ContactPhoneNo, MedicalSummary, JoinDate, BankAccountNo, SortCode) values ('07', 'G0003','Grace','Hargrave','06/05/1958','behamok136@ismailgul.net','EC3A9RB','57 Crown Street Swansea','UK','SA2 6UR','07782114353','NA','01/28/2010','01273801','309493');
insert into Customer (Customerid, GymID, FirstName, Lastname, DOB, Email, Passcode, Homeaddress, HomeCityCode, PostCode, ContactPhoneNo, MedicalSummary, JoinDate, BankAccountNo, SortCode) values ('08', 'G0004','Jamie','Woods','05/18/1979','afqfd3xy5jq@meantinc.com','JM699076D','44 Newport Road Carmarthen','UK','SA31 8QY','07065132959','Heart Condition','4/6/2020','01273801','309493');
insert into Customer (Customerid, GymID, FirstName, Lastname, DOB, Email, Passcode, Homeaddress, HomeCityCode, PostCode, ContactPhoneNo, MedicalSummary, JoinDate, BankAccountNo, SortCode) values ('09', 'G0001','Grace','Hargrave','06/05/1958','behamok136@ismailgul.net','EC3A9RB','57 Crown Street Swansea','UK','SA2 6UR','07782114353','NA','01/28/2010','67037135','600920');
insert into Customer (Customerid, GymID, FirstName, Lastname, DOB, Email, Passcode, Homeaddress, HomeCityCode, PostCode, ContactPhoneNo, MedicalSummary, JoinDate, BankAccountNo, SortCode) values ('10', 'G0002','Evan','George','	8/21/1989','5saq2h4k9d5@powerencry.com','OW076852D','68 Greyfriars Road Cardiff','UK','CF1 3YQ','07051711863','No Medical Conditions','4/24/2020','10909132','070116');
insert into Customer (Customerid, GymID, FirstName, Lastname, DOB, Email, Passcode, Homeaddress, HomeCityCode, PostCode, ContactPhoneNo, MedicalSummary, JoinDate, BankAccountNo, SortCode) values ('11', 'G0003','Jasmine','Day','2/11/1970','ect7v01s7f@bt.net','RT097789','58 Guild Street London','UK','E2 9YY','07849149377','Blood Pressure tablets','4/25/2020','03367219','090126');
insert into Customer (Customerid, GymID, FirstName, Lastname, DOB, Email, Passcode, Homeaddress, HomeCityCode, PostCode, ContactPhoneNo, MedicalSummary, JoinDate, BankAccountNo, SortCode) values ('12', 'G0004','Spencer','Tyler','3/20/1998','w5xpcohr9j@groupmail.com','YN029728A','59 Newport Road Carmarthen','UK','SA318AP','07889350339','No Conditions','5/11/2019','17068859','309493');
insert into Customer (Customerid, GymID, FirstName, Lastname, DOB, Email, Passcode, Homeaddress, HomeCityCode, PostCode, ContactPhoneNo, MedicalSummary, JoinDate, BankAccountNo, SortCode) values ('13', 'G0001','Harvey','Holden','5/15/1986','zzooeak80sr@mail.com','XA192769','58 Golf Road Swansea','UK','SA1 3DB','07905180017','Blood Pressure Tablets','5/22/2018','01273801','309493');
insert into Customer (Customerid, GymID, FirstName, Lastname, DOB, Email, Passcode, Homeaddress, HomeCityCode, PostCode, ContactPhoneNo, MedicalSummary, JoinDate, BankAccountNo, SortCode) values ('14', 'G0002','Jennifer','Sharp','06/05/1958','meitqo7c9xf@msn.net','EC3A9RB','57 Crown Street Cardiff','UK','SA2 6UR','07782114353','NA','01/28/2010','70328725','080054');
insert into Customer (Customerid, GymID, FirstName, Lastname, DOB, Email, Passcode, Homeaddress, HomeCityCode, PostCode, ContactPhoneNo, MedicalSummary, JoinDate, BankAccountNo, SortCode) values ('15', 'G0003','Chris','Needs','5/18/1979','behamok136@ismailgul.net','KK948208B','16 Crown Street London','UK','WC1B 6YR','07058612269','Doctor Referral','6/21/2020','02265768','080022');
insert into Customer (Customerid, GymID, FirstName, Lastname, DOB, Email, Passcode, Homeaddress, HomeCityCode, PostCode, ContactPhoneNo, MedicalSummary, JoinDate, BankAccountNo, SortCode) values ('16', 'G0004','Brandon','Warner','6/22/1965','v6jyfwqm5kc@powerencry.com','HC823541A','5 Newport Road Carmarthen','UK','SA31 1TA','07782114353','NA','01/28/2010','01273801','309493');

--Delete From Customer

Delete FROM Customer

-- Select Data From Customer

SELECT Customerid, GymID, FirstName, Lastname, DOB, Email, Passcode, Homeaddress, HomeCityCode, PostCode, ContactPhoneNo, MedicalSummary, JoinDate, BankAccountNo, SortCode FROM Customer;

Go

--Delete From MembershipPurchase


Delete FROM GymMembershipPrice

--Insert Data Into MembershipPurchase

insert into MembershipPurchase(PurchaseNo, CustomerID, MembershipTypeID, StartDate, EndDate, PurchaseDate, JoiningFeePrice, OneTimeFeePrice, MonthlyFeePrice, MonthlyPaymentDay, LastPaymentDate, Status) values ('P001','01', '01','01/01/2001','12/31/2001','12/25/2000','15.00','456.00','54.00','M','12/31/2001','A');
insert into MembershipPurchase(PurchaseNo, CustomerID, MembershipTypeID, StartDate, EndDate, PurchaseDate, JoiningFeePrice, OneTimeFeePrice, MonthlyFeePrice, MonthlyPaymentDay, LastPaymentDate, Status) values ('P002','02', '02','01/01/2002','12/31/2003','01/01/2001','00.00','384.00','32.00','W','12/31/2002','C');
insert into MembershipPurchase(PurchaseNo, CustomerID, MembershipTypeID, StartDate, EndDate, PurchaseDate, JoiningFeePrice, OneTimeFeePrice, MonthlyFeePrice, MonthlyPaymentDay, LastPaymentDate, Status) values ('P003','03', '03','12/30/2010','01/01/2011','12/31/2011','15.00','456.00','54.00','M','12/31/2001','A');
insert into MembershipPurchase(PurchaseNo, CustomerID, MembershipTypeID, StartDate, EndDate, PurchaseDate, JoiningFeePrice, OneTimeFeePrice, MonthlyFeePrice, MonthlyPaymentDay, LastPaymentDate, Status) values ('P004','04', '04','05/11/2019','05/5/2019','05/5/2019','0.00','0.00','06.25','S','5/11/2019','C');

-- Select Data From Membership Purchase

Select PurchaseNo, CustomerID, MembershipTypeID, StartDate, EndDate, PurchaseDate, JoiningFeePrice, OneTimeFeePrice, MonthlyFeePrice, MonthlyPaymentDay, LastPaymentDate, Status FROM MembershipPurchase

SELECT CustomerID FROM Customer;

SELECT CustomerID, MembershipTypeID
FROM MembershipPurchase
WHERE LastPaymentDate='12/31/2002'

SELECT CustomerID, Postcode,HomeCityCode
FROM Customer
WHERE HomeCityCode='UK'

-- SQL code to demonstrate evidence of testing carried out to validate the constraints
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS;  
GO  


-- SQL code to demonstrate evidence of transaction rollback and commit operations

-- creating a transaction to handle the action(s) needed to purchase a new fixed-term, pay-monthly gym membership contract for an existing customer 
ROLLBACK 
GO
BEGIN TRANSACTION
UPDATE MembershipPurchase
SET MembershipTypeID='01'
WHERE CustomerID IN (
SELECT MembershipTypeID FROM MembershipPurchase WHERE MembershipTypeID LIKE '%4%')
COMMIT TRANSACTION

-- update the customers direct-debit account details in a single transaction
ROLLBACK
GO
BEGIN TRANSACTION TR2
UPDATE Customer
SET BankAccountNo='021000021'
WHERE BankAccountNo IN (
SELECT BankAccountNo FROM Customer WHERE BankAccountNo LIKE '%12098709%')
UPDATE Customer
SET SortCode='200000'
WHERE SortCode IN (
SELECT SortCode FROM Customer WHERE SortCode LIKE '%185008%')
COMMIT TRAN

